﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Rights_CanAccessByRole_V2]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Rights_CanAccessByRole_V2];
GO
CREATE PROCEDURE [dbo].[sproc_Rights_CanAccessByRole_V2]
    @ClassID    int,
    @StaffID    int,
    @Act_ID     int,
    @AllowAccess bit output
/*

======================================================================
功能:    得到角色中@UserName是否能对@ClassID组进行@Act_ID操作
参数:
    @ClassID int        :    组（类）ID
    @StaffID int        :    用户编号
    @Act_ID int         :    权利代号

备注:    继承指是否继承在父组中的权利(在父组中有权利即在此组中有权利)
======================================================================

*/
AS
BEGIN
SET NOCOUNT ON;

SET @AllowAccess = 0;

--此用户的角色能否直接访问全局类(0)？
IF EXISTS(
    SELECT 1 
        FROM 
            dbo.UDS_Role a,
            dbo.UDS_Staff_In_Role b,        
            dbo.UDS_Assign_Rule e
        WHERE
            a.Role_ID = b.Role_ID
            and b.staff_id = @StaffID
            and e.based_on = 3 /* role */
            and e.role_id = a.role_id 
            and e.act_id = @act_id
            and e.team_id = 0  /* 0 indicates global class */
)
BEGIN
    SET @AllowAccess = 1;
    RETURN;
END

--以下判断是判断用户是否再任何一个能访问此类的父类的角色中(能访问父类就能访问子类)
--得到所有父组中拥有此权利的角色ID
DECLARE @pAssignedRole TABLE (RoleID int not null);
INSERT INTO @pAssignedRole (RoleID)
    SELECT Role_ID 
        FROM 
            dbo.UDS_Assign_Rule A
            INNER JOIN dbo.UDS_Class B ON A.Team_ID = B.ClassID
        WHERE
            A.based_on = 3 /* role */
            AND A.act_id = @Act_id
            AND B.ClassID IN (
                SELECT ClassID FROM dbo.fn_get_parent_class(@ClassID)
            );

--用户是否在有权利的角色ID中?
IF EXISTS (
    SELECT 1
    FROM 
        dbo.UDS_Staff_in_Role b
        INNER JOIN @pAssignedRole c ON b.Role_ID = c.RoleID
    WHERE
        b.staff_id = @StaffID
)
BEGIN
    SET @AllowAccess = 1;
END

END
GO